/**
 * 
 */
package mdb.com.vn.persist;

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.springframework.stereotype.Repository;

import mdb.com.vn.model.Loans;
import mdb.com.vn.utils.DateUtils;

/**
 * @author tu.dod
 *
 */
@Repository("loansDAO")
public class LoansDAOImpl extends AbstractHibernateDAOImpl<Loans, Integer> implements LoansDAO,Serializable{

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;

	@Override
	protected Class<Loans> getDomainClass() {
		// TODO Auto-generated method stub
		return Loans.class;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Loans> findByPersionalType(int type) {
		// TODO Auto-generated method stub
		return getSession().getNamedQuery("Loans.findByPersionalType").setInteger("customerType", type).list();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Loans> findByCorporateType(int type) {
		// TODO Auto-generated method stub
		return getSession().getNamedQuery("Loans.findByPersionalType").setInteger("customerType", type).list();
	}

	@Override
	public void addLoansLuckyDrawByScript(String createdBy, Date processDate) {
		try {
			String strDate = DateUtils.format(processDate, "dd-MMM-yyyy");
			Query query = getSession().createSQLQuery("CALL mdbpks_bpro_services.pr_generate_lkd_loan_period(:createdBy,:processDate)");
			query.setParameter("createdBy", createdBy);
			query.setParameter("processDate", strDate);
			query.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Loans> findByStatusLoans(String status) {
		// TODO Auto-generated method stub
		return getSession().getNamedQuery("Loans.findByStatus").setString("status", status).list();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Loans> findByLoansProcessDateAndStaus(String status,
			Date processDate) {
		// TODO Auto-generated method stub
		return getSession().getNamedQuery("Loans.findByPDateStatus").setString("status", status).setDate("processDate", processDate).list();
	}

	@Override
	public Date getLastUpdated(String module) {
		// TODO Auto-generated method stub
		Query query = getSession().createSQLQuery("select max(RUN_DATE) FROM IFTB_GEN_LUCKY_DRAW_LOG where module='"+module+"'");
		return ((Date)query.list().get(0));
	
	}

	@SuppressWarnings("unchecked")
	@Override
	public BigDecimal sumAmount(String status) {
		// TODO Auto-generated method stub
		List<BigDecimal> longs = getSession().getNamedQuery("Loans.sumAmount").setString("status", status).list();
		if (longs.size() > 0){
			if(longs.get(0)!=null){
				return longs.get(0);
			}
		}
		return new BigDecimal(0);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Date> getDatesUpdated(String module) {
		Query query = getSession().createSQLQuery("select RUN_DATE FROM IFTB_GEN_LUCKY_DRAW_LOG where module='"+module+"' order by RUN_DATE desc");
		return query.list();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object> findByPropertiesToExcell(String status, Date processDate) {
		
		String sql =  "SELECT DISTINCT (L.REF_NO) AS LD," 
		+"COP.COMPANY_NAME AS BRANCH,"
		+"L.CREATED_DATE AS CREATED_DATE,"
		+"CUS.CUSTOMER_NAME AS CUSTOMER,"
		+"L.CUSTOMER_ID AS GCIF,"
		+"CUS.PHONE_COUNTRY_CODE || CUS.PHONE_AREA_CODE ||CUS.PHONE_NUMBER AS PHONE,"
		+"L.PR_AMOUNT AS AMOUNT,"
		+"L.INTEREST_RATE AS RATE,"
		+"L.CATEGORY || L.SUB_PRODUCTCODE AS PRODUCT,"
		+"L.TERM AS TERM,"
		+"L.TXN_DESC AS DEALER,"
		+"L.CUSTOMER_TYPE AS CUSTOMER_TYPE,"
		+"L.LKD_NUM AS NUMLKD"
		+",GET_LUCKY_DRAW_NUM(L.REF_NO) AS LDK_DETAILS "
		+"FROM IFTB_LOANS L,IFTB_CUSTOMER_DETAIL CUS,IFTB_COMPANY COP "
		+"WHERE L.CUSTOMER_ID = CUS.GCIF AND CUS.BRANCH_CODE = COP.ID AND L.STATUS = :STATUS AND L.PROCESS_DATE = :PROCESS_DATE";
		
		return getSession().createSQLQuery(sql)
				.addScalar("LD", Hibernate.STRING)
				.addScalar("BRANCH", Hibernate.STRING)
				.addScalar("CREATED_DATE", Hibernate.DATE)
				.addScalar("CUSTOMER", Hibernate.STRING)
				.addScalar("GCIF", Hibernate.STRING)
				.addScalar("PHONE", Hibernate.STRING)
				.addScalar("AMOUNT", Hibernate.DOUBLE)
				.addScalar("RATE", Hibernate.DOUBLE)
				.addScalar("PRODUCT", Hibernate.STRING)
				.addScalar("TERM", Hibernate.STRING)
				.addScalar("DEALER", Hibernate.STRING)
				.addScalar("CUSTOMER_TYPE", Hibernate.STRING)
				.addScalar("NUMLKD", Hibernate.STRING)
				.addScalar("LDK_DETAILS", Hibernate.STRING).setString("STATUS", status).setDate("PROCESS_DATE", processDate).list();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object> findByPropertiesToExcell(String status) {
		String sql =  "SELECT DISTINCT (L.REF_NO) AS LD," 
		+"COP.COMPANY_NAME AS BRANCH,"
		+"L.CREATED_DATE AS CREATED_DATE,"
		+"CUS.CUSTOMER_NAME AS CUSTOMER,"
		+"L.CUSTOMER_ID AS GCIF,"
		+"CUS.PHONE_COUNTRY_CODE || CUS.PHONE_AREA_CODE ||CUS.PHONE_NUMBER AS PHONE,"
		+"L.PR_AMOUNT AS AMOUNT,"
		+"L.INTEREST_RATE AS RATE,"
		+"L.CATEGORY || L.SUB_PRODUCTCODE AS PRODUCT,"
		+"L.TERM AS TERM,"
		+"L.TXN_DESC AS DEALER,"
		+"L.CUSTOMER_TYPE AS CUSTOMER_TYPE,"
		+"L.LKD_NUM AS NUMLKD"
		+",GET_LUCKY_DRAW_NUM(L.REF_NO) AS LDK_DETAILS "
		+"FROM IFTB_LOANS L,IFTB_CUSTOMER_DETAIL CUS,IFTB_COMPANY COP "
		+"WHERE L.CUSTOMER_ID = CUS.GCIF AND CUS.BRANCH_CODE = COP.ID AND L.STATUS = :STATUS";
		
		return getSession().createSQLQuery(sql)
				.addScalar("LD", Hibernate.STRING)
				.addScalar("BRANCH", Hibernate.STRING)
				.addScalar("CREATED_DATE", Hibernate.DATE)
				.addScalar("CUSTOMER", Hibernate.STRING)
				.addScalar("GCIF", Hibernate.STRING)
				.addScalar("PHONE", Hibernate.STRING)
				.addScalar("AMOUNT", Hibernate.DOUBLE)
				.addScalar("RATE", Hibernate.DOUBLE)
				.addScalar("PRODUCT", Hibernate.STRING)
				.addScalar("TERM", Hibernate.STRING)
				.addScalar("DEALER", Hibernate.STRING)
				.addScalar("CUSTOMER_TYPE", Hibernate.STRING)
				.addScalar("NUMLKD", Hibernate.STRING)
				.addScalar("LDK_DETAILS", Hibernate.STRING).setString("STATUS", status).list();
	}
}
